package com.suypower.carManage.comm;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 *
 * @author zhaoyuan
 */
public class DAO {

    private ResultSet rs;
    private Statement stm;
    private Connection con;

    public DAO() {
        try {
            Class.forName(Connect.DRIVER);
        } catch (Exception e) {
        }
    }

    private void closeCon() {
        try {
            stm.close();
            con.close();
        } catch (SQLException e) {
        }
    }

    private Connection getCon() {
        try {
            Connection getCon = DriverManager.getConnection(Connect.URL, Connect.USER, Connect.PASSWORD);
            return getCon;
        } catch (SQLException e) {
            return null;
        }
    }

    private Statement getStm() {
        try {
            con = this.getCon();
            Statement getStm = con.createStatement();
            return getStm;
        } catch (Exception e) {
            return null;
        }
    }

    public Vector getDataSet(String SQL) throws SQLException {
        /*将结果集转化成Vector
         * method name:getDataSet return type:Vector(Hashtable<String,String>)
         * for example:
         * String sql="select * from temp"
         * DAO dao=new DAO();
         * try{
         *    Vector v=dao.getDataSet(sql);
         *    System.out.print(v);
         * }catch(SQLException e){}
         *   the output result is:
         *[{ID=12, VALUE=12}, {ID=13, VALUE=13}]
         */
        if (SQL != null && SQL.length() > 6) {
            SQL = SQL.trim();
            String judge = SQL.substring(0, 6).toUpperCase();
            if (judge.equals("SELECT")) {
                stm = getStm();
                try {
                    rs = stm.executeQuery(SQL);
                } catch (Exception e) {
                    System.out.println("getDataSet exception:java.sql.SQLException: result set is null.");
                    System.out.println("getDataSet exception:" + SQL);
                    closeCon();
                    return null;
                }
                Vector vector = new Vector();
                ArrayList<String> list = new ArrayList<String>();
                ResultSetMetaData data = rs.getMetaData();
                for (int i = 1; i <= data.getColumnCount(); i++) {
                    list.add(data.getColumnName(i));
                }
                while (rs.next()) {
                    Hashtable<String, String> hash = new Hashtable();
                    for (int i = 0; i < list.size(); i++) {
                        if (rs.getString(list.get(i)) != null) {
                            hash.put(list.get(i).toUpperCase(), rs.getString(list.get(i)));
                        }
                    }
                    vector.add(hash);
                }
                try {
                    rs.close();
                } catch (Exception e) {
                    System.out.println(e.fillInStackTrace());
                }
                closeCon();
                return vector;
            } else {
                System.out.println("getDataSet exception:java.sql.SQLException: this SQL statement have no 'SELECT'.");
                return null;
            }
        } else {
            System.out.println("This is not a valid SQL statement");
            return null;
        }
    }

    public Vector getDataSet(String SQL, int pageCode, int pageSize) throws SQLException {
        /*  在返回的结果集里添加分页功能
         * method name:getDataSet return type:Vector(Hashtable<String,String>)
         * for example:
         * String sql="select * from temp"
         * DAO dao=new DAO();
         * try{
         *    Vector v=dao.getDataSet(sql,1,4);
         *    System.out.print(v);
         * }catch(SQLException e){}
         *   the output result is:
         *[{ID=1, VALUE=！}, {ID=2, VALUE=”}, {ID=3, VALUE=#}, {ID=4, VALUE=$}]
         */
        int rowStart = (pageCode - 1) * pageSize + 1;
        int pageCtrl = 0;
        if (SQL != null && SQL.length() > 6) {
            SQL = SQL.trim();
            String judge = SQL.substring(0, 6).toUpperCase();
            if (judge.equals("SELECT")) {
                stm = getStm();
                try {
                    rs = stm.executeQuery(SQL);
                } catch (Exception e) {
                    System.out.println("getDataSet exception:java.sql.SQLException: result set is null.");
                    System.out.println("getDataSet exception:" + SQL);
                    closeCon();
                    return null;
                }
                Vector vector = new Vector();
                ArrayList<String> list = new ArrayList<String>();
                ResultSetMetaData data = rs.getMetaData();
                for (int i = 1; i <= data.getColumnCount(); i++) {
                    list.add(data.getColumnName(i));
                }
                rs.absolute(rowStart);
                rs.previous();
                while (rs.next()) {
                    pageCtrl++;
                    Hashtable<String, String> hash = new Hashtable();
                    for (int i = 0; i < list.size(); i++) {
                        hash.put(list.get(i).toUpperCase(), rs.getString(list.get(i)));
                    }
                    vector.add(hash);
                    if (pageCtrl == pageSize) {
                        break;
                    }
                }
                try {
                    rs.close();
                } catch (Exception e) {
                    System.out.println(e.fillInStackTrace());
                }
                closeCon();
                return vector;
            } else {
                System.out.println("getDataSet exception:java.sql.SQLException: this SQL statement have no 'SELECT'.");
                return null;
            }
        } else {
            System.out.println("This is not a valid SQL statement");
            return null;
        }
    }

    public int getDataSetCount(String SQL) throws SQLException {
        /*   计算结果集行数
         * method name:getDataSet return type:int
         * for example:
         * String sql="select * from temp"
         * DAO dao=new DAO();
         * try{
         *    int count =dao.getDataSetCount(sql);
         *    System.out.print(count);
         * }catch(SQLException e){}
         *   the output result is:
         *   4
         */
        if (SQL != null && SQL.length() > 6) {
            SQL = SQL.trim();
            String judge = SQL.substring(0, 6).toUpperCase();
            if (judge.equals("SELECT")) {
                stm = getStm();
                try {
                    rs = stm.executeQuery(SQL);
                } catch (Exception e) {
                    System.out.println("getDataSet exception:java.sql.SQLException: result set is null.");
                    System.out.println("getDataSet exception:" + SQL);
                    closeCon();
                    return 0;
                }
                try {
                    rs.last();
                    int count = rs.getRow();
                    return count;
                } catch (SQLException e) {
                    System.out.println(e.fillInStackTrace());
                    return 0;
                } finally {
                    closeCon();
                }
            } else {
                System.out.println("getDataSet exception:java.sql.SQLException: this SQL statement have no 'SELECT'.");
                return 0;
            }
        } else {
            System.out.println("This is not a valid SQL statement");
            return 0;
        }
    }

    public String getDataValue(String SQL) throws SQLException {
        /* 返回结果集里第一行第一列的值
         * method name:getDataSet return type:String
         * for example:
         * String sql="select * from temp"
         * DAO dao=new DAO();
         * try{
         *   String str  =dao.getDataValue(sql);
         *    System.out.print(str);
         * }catch(SQLException e){}
         *   the output result is:
         *   mystr
         */
        if (SQL != null && SQL.length() > 6) {
            SQL = SQL.trim();
            String judge = SQL.substring(0, 6).toUpperCase();
            if (judge.equals("SELECT")) {
                stm = getStm();
                try {
                    rs = stm.executeQuery(SQL);
                } catch (Exception e) {
                    System.out.println("getDataSet exception:java.sql.SQLException: result set is null.");
                    System.out.println("getDataSet exception:" + SQL);
                    closeCon();
                    return null;
                }
                try {
                    String str = null;
                    while (rs.next()) {
                        str = rs.getString(1);
                        break;
                    }
                    return str;
                } catch (SQLException e) {
                    System.out.println(e.fillInStackTrace());
                    return null;
                } finally {
                    closeCon();
                }
            } else {
                System.out.println("getDataSet exception:java.sql.SQLException: this SQL statement have no 'SELECT'.");
                return null;
            }
        } else {
            System.out.println("This is not a valid SQL statement");
            return null;
        }
    }

    public int execCommand(String SQL) throws SQLException {
        /*
         *    执行单个SQL语句，返回受影响的行数
         */
        stm = getStm();
        boolean b;
        try {
            int count = stm.executeUpdate(SQL);
            closeCon();
            return count;
        } catch (Exception e) {
            closeCon();
            System.out.println(e.fillInStackTrace());
            return 0;
        }
    }

    public boolean execCommand(ArrayList list) throws SQLException {
        /*
         *执行一组SQL语句（使用ArrayList），返回执行的结果 （true：执行成功，false：执行失败）只要有一次失败即定义成失败
         */
        if (list != null) {
            stm = getStm();
            for (int i = 0; i < list.size(); i++) {
                try {
                    stm.addBatch((String) list.get(i));
                } catch (SQLException e) {
                    continue;
                }
            }
            try {
                int[] count = stm.executeBatch();
                boolean b = true;
                for (int i : count) {
                    if (i <= 0) {
                        b = false;
                    }
                }
                closeCon();
                return b;
            } catch (Exception e) {
                closeCon();
                System.out.println(e.fillInStackTrace());
                return false;
            }
        } else {
            System.out.println("error!the Arraylist is null");
            return false;
        }
    }

    public int[] execCommands(ArrayList list) throws SQLException {
        /*
         *执行一组SQL语句（使用ArrayList），返回执行的结果（int[] 类型，即每一个SQL执行后产生影响的行数）
         */
        if (list != null) {
            stm = getStm();
            for (int i = 0; i < list.size(); i++) {
                try {
                    stm.addBatch((String) list.get(i));
                } catch (SQLException e) {
                    continue;
                }
            }
            try {
                int[] count = stm.executeBatch();
                closeCon();
                return count;
            } catch (Exception e) {
                closeCon();
                System.out.println(e.fillInStackTrace());
                return null;
            }
        } else {
            System.out.println("error!the Arraylist is null");
            return null;
        }
    }

    public boolean execCommand(String[] sql) throws SQLException {
        /*
         *执行一组SQL语句（使用Array），返回执行的结果 （true：执行成功，false：执行失败），只要有一次失败即定义成失败
         */
        if (sql != null) {
            stm = getStm();
            for (int i = 0; i < sql.length; i++) {
                try {
                    stm.addBatch(sql[i]);
                } catch (SQLException e) {
                }
            }
            try {
                int[] count = stm.executeBatch();
                boolean b = true;
                for (int i : count) {
                    if (i <= 0) {
                        b = false;
                    }
                }
                closeCon();
                return b;
            } catch (Exception e) {
                System.out.println(e.fillInStackTrace());
                closeCon();
                return false;
            }
        } else {
            System.out.println("the array is null");
            return false;
        }
    }

    public int[] execCommands(String[] sql) throws SQLException {
        /*
         *执行一组SQL语句（使用Array），返回执行的结果（int[] 类型，即每一个SQL执行后产生影响的行数）
         */
        if (sql != null) {
            stm = getStm();
            for (int i = 0; i < sql.length; i++) {
                try {
                    stm.addBatch(sql[i]);
                } catch (SQLException e) {
                }
            }
            try {
                int[] count = stm.executeBatch();
                closeCon();
                return count;
            } catch (Exception e) {
                System.out.println(e.fillInStackTrace());
                closeCon();
                return null;
            }
        } else {
            System.out.println("the array is null");
            return null;
        }
    }

    public void changeAb() {
        String sql = "select * from tb_driver";
        ArrayList<String> sqlList = new ArrayList<String>();
        DAO dao = new DAO();
        try {
            Vector v = dao.getDataSet(sql);
            if (v != null) {
                Iterator<Hashtable> iterator = v.iterator();
                while (iterator.hasNext()) {
                    Hashtable hashTable = iterator.next();
                    String name = (String) hashTable.get("NAME");
                    String id = (String) hashTable.get("ID");
                    GB2Alpha obj1 = new GB2Alpha();
                    String ab = obj1.String2Alpha(util.DeleteSpace(name));
                    System.out.println(ab);
                    sqlList.add("update tb_driver set ab='" + ab + "' where id=" + id);
                }
            }
            dao.execCommand(sqlList);
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void changeLogo() {
        String sql = "SELECT * FROM tb_car";
        DAO dao = new DAO();
        ArrayList<String> sqlList = new ArrayList<String>();
        try {
            Vector v = dao.getDataSet(sql);
            if (v != null) {
                Iterator<Hashtable> iterator = v.iterator();
                while (iterator.hasNext()) {
                    Hashtable hashtable = iterator.next();
                    String id = (String) hashtable.get("ID");
                    String logo = (String) hashtable.get("LOGO");
                    String gps = (String) hashtable.get("GPS");
                    logo=judgeYesOrNo(logo);
                    gps=judgeYesOrNo(gps);
                    sqlList.add("update tb_car set logo='" + logo + "' where id=" + id);
                     sqlList.add("update tb_car set gps='" + gps + "' where id=" + id);
                }
            }
            dao.execCommand(sqlList);
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public String judgeYesOrNo(String str) {
        String res = "";
        if (str != null) {
            if (str.contains("是") || str.contains("yes")) {
                res="1";
            }else{
                res="2";
            }
        } else {
            res="2";
        }

        return res;
    }
}

class Connect {

    public static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    public static final String URL = "jdbc:sqlserver://localhost:1435;DatabaseName=carManage";
    public static final String USER = "sa";
    public static final String PASSWORD = "jsepc01!";
}

class GB2Alpha {

    //字母Z使用了两个标签，这里有２７个值
    //i, u, v都不做声母, 跟随前面的字母
    private char[] chartable
            = {
                '啊', '芭', '擦', '搭', '蛾', '发', '噶', '哈', '哈',
                '击', '喀', '垃', '妈', '拿', '哦', '啪', '期', '然',
                '撒', '塌', '塌', '塌', '挖', '昔', '压', '匝', '座'
            };

    private char[] alphatable
            = {
                'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I',
                'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R',
                'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'
            };

    private int[] table = new int[27];

    //初始化
    {
        for (int i = 0; i < 27; ++i) {
            table[i] = gbValue(chartable[i]);
        }
    }

    public GB2Alpha() {
    }

    //主函数,输入字符,得到他的声母,
    //英文字母返回对应的大写字母
    //其他非简体汉字返回 '0'
    public char Char2Alpha(char ch) {

        if (ch >= 'a' && ch <= 'z') {
            return (char) (ch - 'a' + 'A');
        }
        if (ch >= 'A' && ch <= 'Z') {
            return ch;
        }
        int gb = gbValue(ch);
        if (gb < table[0]) {
            return '0';
        }
        int i;
        for (i = 0; i < 26; ++i) {
            if (match(i, gb)) {
                break;
            }
        }
        if (i >= 26) {
            return '0';
        } else {
            return alphatable[i];
        }
    }

    //根据一个包含汉字的字符串返回一个汉字拼音首字母的字符串
    public String String2Alpha(String SourceStr) {

        String Result = "";
        int StrLength = SourceStr.length();
        int i;
        try {
            for (i = 0; i < StrLength; i++) {
                Result += Char2Alpha(SourceStr.charAt(i));
            }
        } catch (Exception e) {
            Result = "";
        }
        return Result;
    }

    private boolean match(int i, int gb) {

        if (gb < table[i]) {
            return false;
        }
        int j = i + 1;

        //字母Z使用了两个标签
        while (j < 26 && (table[j] == table[i])) {
            ++j;
        }
        if (j == 26) {
            return gb <= table[j];
        } else {
            return gb < table[j];
        }
    }

    //取出汉字的编码
    private int gbValue(char ch) {

        String str = new String();
        str += ch;
        try {
            byte[] bytes = str.getBytes("GB2312");
            if (bytes.length < 2) {
                return 0;
            }
            return (bytes[0] << 8 & 0xff00) + (bytes[1]
                    & 0xff);
        } catch (Exception e) {
            return 0;
        }
    }

    public static void main(String[] args) {

        GB2Alpha obj1 = new GB2Alpha();
        System.out.println(obj1.String2Alpha("赵远"));
    }
}

class util {

    public static String DeleteSpace(String str) {
        String res = "";
        for (int i = 0; i < str.length(); i++) {
            char c = str.charAt(i);
            if (c == '（') {
                break;
            }

            if (c == '(') {
                break;
            }
            if (c != ' ') {
                res += c;
            }

        }
        return res;
    }
}
